package com.gm.baseplatform.core.jdbc;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class BaseDAO {
    private static final Log LOG = LogFactory.getLog(BaseDAO.class);

    private JDBCWrapper jw;

    public BaseDAO() {
    }

    
    public void open() throws SQLException {
        try {
            if (jw != null)
                return;
            jw = new JDBCWrapper();
            jw.setAutoCommit(false);
        } catch (NamingException e) {
            throw new SQLException(e.getMessage());
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
    }

    
    public void openUM() throws SQLException {
        String UMJndiName = "jdbc/Ora9iUM";
        try {
        this.open(UMJndiName);
        }catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
    }


    public void open(String jndiName) throws SQLException {
        try {
            if (jw != null)
                return;
            jw = new JDBCWrapper(jndiName);
            jw.setAutoCommit(false);
        } catch (NamingException e) {
            throw new SQLException(e.getMessage());
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
    }

    
    public void close() {
        try {
            if (jw != null) {
                jw.close();
                jw = null;
            }
        } catch (SQLException e) {
            LOG.error("close()", e);
        }
    }

    
    protected void prepareStatment(String sql) throws SQLException {
        jw.prepareStatement(sql);
    }

    
    protected void setString(int index, String value) throws SQLException {
        jw.setString(index, value);
    }

    
    protected void setInt(int index, int value) throws SQLException {
        jw.setInt(index, value);
    }


    protected void setBoolean(int index, boolean value) throws SQLException {
        jw.setBoolean(index, value);
    }


    protected void setDate(int index, Date value) throws SQLException {
        jw.setDate(index, value);
    }


    protected void setLong(int index, long value) throws SQLException {
        jw.setLong(index, value);
    }


    protected void setFloat(int index, float value) throws SQLException {
        jw.setFloat(index, value);
    }

    
    protected void setClob(int index, Clob value) throws SQLException {
        jw.setClob(index, value);
    }


    protected void setObject(int index, Object value) throws SQLException {
        jw.setObject(index, value);
    }


    protected void setTimestamp(int index, java.sql.Timestamp value)
            throws SQLException {
        jw.setTimestamp(index, value);
    }


    protected void setCharacterStream(int index, Reader reader, int length)
            throws SQLException {
        jw.setCharacterStream(index, reader, length);
    }


    protected int doSQLUpdate() throws SQLException {
        int r = jw.executeUpdate();
        return r;
    }


    protected String[][] doSQLQuery() throws SQLException {
        String sTmp;
        String[][] sArrRtn = null;
        int iCol = 0, i = 0, j = 0;
        boolean bFlg = false;

        ResultSet rs = null;
        ResultSetMetaData rsmd = null;

        try {
            rs = jw.executeQuery();
            rsmd = rs.getMetaData();
            iCol = rsmd.getColumnCount();

            bFlg = rs.last();
            if (bFlg)
                i = rs.getRow();
            rs.beforeFirst();
            sArrRtn = new String[i + 1][iCol];
            for (i = 0; i < iCol; i++) {
                sTmp = rsmd.getColumnName(i + 1).trim();
                sArrRtn[0][i] = sTmp;
            }
            while (rs.next()) {
                j = j + 1;
                for (i = 0; i < iCol; i++) {
                    sTmp = String.valueOf(
                            rs.getObject(i + 1) == null ? "" : rs
                                    .getObject(i + 1)).trim();
                    if (sTmp.equals("null"))
                        sTmp = "";
                    sArrRtn[j][i] = sTmp;
                }
            }
        } catch (SQLException se) {
            throw se;
        } catch (Exception e) {
            LOG.error(e);
        }
        return sArrRtn;
    }
    

    protected String[][] doClobSQLQuery(String sql) throws SQLException{
      String sTmp="";
      String[][] sArrRtn = null;
      int iCol = 0, i = 0, j = 0;
      boolean bFlg = false;

      Statement st = null;
      ResultSet rs = null;
      ResultSetMetaData rsmd = null;

      try {
        jw.createStatement();
        rs = jw.executeQuery(sql);
        rsmd = rs.getMetaData();
        iCol = rsmd.getColumnCount();

        bFlg = rs.last();
        if (bFlg) i = rs.getRow();

        rs.beforeFirst();
        sArrRtn = new String[i + 1][iCol];
        for (i = 0; i < iCol; i++) {
          sTmp = rsmd.getColumnName(i + 1).trim();
          sArrRtn[0][i] = sTmp;
        }

        while (rs.next()) {
          j = j + 1;
          for (i = 0; i < iCol; i++) {

            if (sArrRtn[0][i].equals("ITEM_VALUE_TEXT")||sArrRtn[0][i].equals("BODY")){
              oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(sArrRtn[0][i]);
              if(clob!=null){
                  sTmp = "";
                  StringBuffer sb = new StringBuffer();
                  Reader is = clob.getCharacterStream();
                  BufferedReader br = new BufferedReader(is);
                  String s = br.readLine();
                  while(s!=null){
//                      sTmp += s;
                      sb.append(s);
                      s=br.readLine();
                  }
                  sTmp = sb.toString();
              }
            }else{
                sTmp = String.valueOf(
                    rs.getObject(i + 1) == null ? "" : rs.getObject(i + 1)).trim();
                if (sTmp.equals("null"))
                  sTmp = "";
            }

            sArrRtn[j][i] = sTmp;
          }
        }

      } catch (SQLException se) {
        LOG.error(se);
        throw se;
      } catch (Exception e) {
        e.printStackTrace();
        LOG.error(e);
      } finally {
        try {
          rsmd = null;
          if (rs != null) {
            rs.close();
            rs = null;
          }
          if (st != null) {
            st.close();
            st = null;
          }
        } catch (SQLException se) {
          se.printStackTrace();
          throw se;
        }
      }
      return sArrRtn;
    }


    protected String[][] doSQLQuery(String sql) throws SQLException {
        String sTmp;
        String[][] sArrRtn = null;
        int iCol = 0, i = 0, j = 0;
        boolean bFlg = false;

        Statement st = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;

        try {
            jw.createStatement();
            rs = jw.executeQuery(sql);
            rsmd = rs.getMetaData();
            iCol = rsmd.getColumnCount();

            bFlg = rs.last();
            if (bFlg)
                i = rs.getRow();

            rs.beforeFirst();
            sArrRtn = new String[i + 1][iCol];
            for (i = 0; i < iCol; i++) {
                sTmp = rsmd.getColumnName(i + 1).trim();
                sArrRtn[0][i] = sTmp;
            }

            while (rs.next()) {
                j = j + 1;
                for (i = 0; i < iCol; i++) {
                    sTmp = String.valueOf(
                            rs.getObject(i + 1) == null ? "" : rs
                                    .getObject(i + 1)).trim();
                    if (sTmp.equals("null"))
                        sTmp = "";
                    sArrRtn[j][i] = sTmp;
                }
            }

        } catch (SQLException se) {
            LOG.error(se);
            throw se;
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error(e);
        } finally {
            try {
                rsmd = null;
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (st != null) {
                    st.close();
                    st = null;
                }
            } catch (SQLException se) {
                se.printStackTrace();
                throw se;
            }
        }
        return sArrRtn;
    }


    protected String[][] doSQLQuery(String sql, Object[] args)
            throws SQLException {

        String sTmp;
        String[][] sArrRtn = null;
        int iCol = 0, i = 0, j = 0;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        List<String> list = new ArrayList<String>();
        try {
            con = jw.getConnection();
            pst = con.prepareStatement(sql);
            if (args != null) {
                for (int k = 0; k < args.length; k++) {
                    pst.setObject(k + 1, args[k]);
                }
            }
            rs = pst.executeQuery();
            rsmd = rs.getMetaData();
            iCol = rsmd.getColumnCount();
            while (rs.next()) {
                for (i = 0; i < iCol; i++) {
                    sTmp = String.valueOf(
                            rs.getObject(i + 1) == null ? "" : rs
                                    .getObject(i + 1)).trim();
                    if (sTmp.equals("null"))
                        sTmp = "";
                    list.add(sTmp);
                }
                j++;
            }
            sArrRtn = new String[j + 1][iCol];
            for (i = 0; i < iCol; i++) {
                sTmp = rsmd.getColumnName(i + 1).trim();
                sArrRtn[0][i] = sTmp;
            }
            for (int k1 = 0, k2 = 0; k1 < list.size(); k1++, k2++) {
                for (int len = 0; len < iCol; len++) {
                    sArrRtn[k2 + 1][len] = (String) list.get(k1);
                    if (len != iCol - 1) {
                        k1++;
                    }
                }
            }

        } catch (SQLException se) {
            throw se;
        } catch (Exception e) {
            LOG.error(e);
        } finally {
            try {
                rsmd = null;
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
            } catch (SQLException se) {
                throw se;
            }
        }
        return sArrRtn;
    }


    protected int doSQLUpdate(String sql) throws SQLException {
        int r = 0;
        jw.createStatement();
        r = jw.executeUpdate(sql);
        return r;
    }

    
    protected int doSQLUpdate(String sql, Object[] args) throws SQLException {
        int r = 0;
        jw.prepareStatement(sql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                jw.setObject(i + 1, args[i]);
            }
        }
        r = jw.executeUpdate();
        return r;
    }


    public void commit() throws SQLException {
        if (jw != null)
            jw.commit();
    }


    public void rollback() throws SQLException {
        if (jw != null)
            jw.rollback();
    }
}